﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using KPIS.DBM;
using System.ServiceModel.Activation;
using System.ServiceModel.Web;
using KPIS.GERP.Models;
using KPIS.GERP.Models.Class;

namespace KPIS.GERP.WCF.GBMS
{
    [ServiceContract]
    public interface IProjectService
    {
        [OperationContract]
        [WebGet(UriTemplate = "/GetProjectInfo/{ProjectId}", ResponseFormat = WebMessageFormat.Xml)]
        Project GetProjectInfo(int ProjectId);

        [OperationContract]
        [WebGet(UriTemplate = "GetProjectsFromAreaId", ResponseFormat = WebMessageFormat.Xml)]
        ICollection<Project> GetProjects(int? AreaId, int? BudgetId);

        [OperationContract]
        [WebGet(UriTemplate = "GetProjectArea", ResponseFormat = WebMessageFormat.Xml)]
        string[] GetProjectArea(int ProjectId);

        [OperationContract]
        [WebGet(UriTemplate = "GetProjectBudget", ResponseFormat = WebMessageFormat.Xml)]
        string[] GetProjectBudget(int ProjectId);
    }

    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "ProjectService" in code, svc and config file together.
    // NOTE: In order to launch WCF Test Client for testing this service, please select ProjectService.svc or ProjectService.svc.cs at the Solution Explorer and start debugging.
	//[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
	[ServiceErrorBehavior(typeof(ElmahErrorHandler))]
    public class ProjectService : IProjectService
    {
        DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationManager.AppSettings["ConnectionString"]);

        public Project GetProjectInfo(int ProjectId)
        {
            DataTable dtb;
            string strSQL;

            strSQL = "select p.bgm_proj_seq"
                + ", p.bgm_proj_code"
                + ", p.bgm_proj_name"
                + ", p.bgm_proj_descr"
                + ", p.bgm_proj_manager"
                + ", p.bgm_proj_scope"
                + ", p.bgm_proj_date"
                + ", p.bgm_proj_reason"
                + ", p.bgm_proj_object"
                + ", p.bgm_proj_target"
                + ", p.bgm_proj_plan"
                + ", p.bgm_proj_remark"
                + ", p.bgm_year"
                + ", p.date_start"
                + ", p.date_end"
                + ", p.gfmis_code"

                + ", coalesce(pe.total_amount, 0) as total_amount"
                + ", j.job_site_name"
                + ", s.bgm_strat_list_name as sub_act"
                + ", s2.bgm_strat_list_name as act"
                + ", s3.bgm_strat_list_name as out_put"

                + " from bgm_project p"
                + " left join (select bgm_proj_seq"
                    + ", sum(coalesce(total_amount, 0)) as total_amount"
                    + " from bgm_project_expense"
                    + " group by bgm_proj_seq"
                    + ") pe on p.bgm_proj_seq = pe.bgm_proj_seq"
                + " left join hrm_mp_job_sites j on p.job_site_seq = j.job_site_seq"
                + " left join bgm_mas_project_type pt on p.prj_type_seq = pt.prj_type_seq"
                + " left join bgm_strategy_data_mapping mp1 on mp1.bgm_strat_list_seq = p.bgm_strat_list_seq"
                + " left join bgm_strategy_data_mapping mp2 on mp2.bgm_strat_data_map_seq = mp1.upper_bgm_strat_data_map_seq"
                + " left join bgm_strategy_data_mapping mp3 on mp3.bgm_strat_data_map_seq = mp2.upper_bgm_strat_data_map_seq"

                + " left join bgm_strategy_data_list s on p.bgm_strat_list_seq = s.bgm_strat_list_seq"
                + " left join bgm_strategy_data_list s2 on mp2.bgm_strat_list_seq = s2.bgm_strat_list_seq"
                + " left join bgm_strategy_data_list s3 on mp3.bgm_strat_list_seq = s3.bgm_strat_list_seq"
                + " where p.bgm_proj_seq = " + ProjectId;
            dbManager.Open();
            dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            Project model = new Project();
            if (dtb.Rows.Count > 0)
            {
                DataRow dr = dtb.Rows[0];
                model.ProjectId = (int)dr[0];
                model.Code = dr[1].ToString();
                model.Name = dr[2].ToString();
                model.Descr = dr[3].ToString();
                model.Manager = dr[4].ToString();
                model.Scope = dr[5].ToString();
                model.Date = (DateTime?)dr[6];
                model.Reason = dr[7].ToString();
                model.Object = dr[8].ToString();
                model.Target = dr[9].ToString();
                model.Plan = dr[10].ToString();
                model.Remarks = dr[11].ToString();
                model.Year = (int?)dr[12];
                model.StartDate = (DateTime?)dr[13];
                model.EndDate = (DateTime?)dr[14];
                model.GfmisCode = dr[15].ToString();
                model.TotalAmount = (decimal?)dr[16];
                model.JobSiteName = dr[17].ToString();
                model.SubAct = dr[18].ToString();
                model.Act = dr[19].ToString();
                model.OutPut = dr[20].ToString();
            }

            return model;
        }

        public ICollection<Project> GetProjectsFromAreaId(int AreaId)
        {
            throw new NotImplementedException();
        }

        public ICollection<Project> GetProjects(int? AreaId, int? BudgetId)
        {
            DataTable dtb;
            string strSQL;

            strSQL = "select p.bgm_proj_seq"
                + ", p.bgm_proj_code"
                + ", p.bgm_proj_name"
                + ", p.bgm_proj_descr"
                + ", p.bgm_proj_manager"
                + ", p.bgm_proj_scope"
                + ", p.bgm_proj_date"
                + ", p.bgm_proj_reason"
                + ", p.bgm_proj_object"
                + ", p.bgm_proj_target"
                + ", p.bgm_proj_plan"
                + ", p.bgm_proj_remark"
                + ", p.bgm_year"
                + ", p.date_start"
                + ", p.date_end"
                + ", p.gfmis_code"

                + ", coalesce(pe.total_amount, 0) as total_amount"
                + ", j.job_site_name"
                + ", s.bgm_strat_list_name as sub_act"
                + ", s2.bgm_strat_list_name as act"
                + ", s3.bgm_strat_list_name as out_put"

                + " from bgm_project p"
                + " left join (select bgm_proj_seq"
                    + ", sum(coalesce(total_amount, 0)) as total_amount"
                    + " from bgm_project_expense"
                    + " group by bgm_proj_seq"
                    + ") pe on p.bgm_proj_seq = pe.bgm_proj_seq"
                + " left join hrm_mp_job_sites j on p.job_site_seq = j.job_site_seq"
                + " left join bgm_mas_project_type pt on p.prj_type_seq = pt.prj_type_seq"
                + " left join bgm_strategy_data_mapping mp1 on mp1.bgm_strat_list_seq = p.bgm_strat_list_seq"
                + " left join bgm_strategy_data_mapping mp2 on mp2.bgm_strat_data_map_seq = mp1.upper_bgm_strat_data_map_seq"
                + " left join bgm_strategy_data_mapping mp3 on mp3.bgm_strat_data_map_seq = mp2.upper_bgm_strat_data_map_seq"

                + " left join bgm_strategy_data_list s on p.bgm_strat_list_seq = s.bgm_strat_list_seq"
                + " left join bgm_strategy_data_list s2 on mp2.bgm_strat_list_seq = s2.bgm_strat_list_seq"
                + " left join bgm_strategy_data_list s3 on mp3.bgm_strat_list_seq = s3.bgm_strat_list_seq"
                + " inner join bgm_project_option po on p.bgm_proj_seq = po.bgm_proj_seq"
                + " inner join bgm_mas_option opt on po.bgm_option_seq = opt.bgm_option_seq"
                + " where p.record_status <> 'D'"
                + (AreaId != null ? " and opt.bgm_option_code = " + AreaId : "")
                + (BudgetId != null ? " and opt.bgm_option_code = " + BudgetId : "");
            dbManager.Open();
            dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            ICollection<Project> models = new List<Project>();

            foreach (DataRow dr in dtb.Rows)
            
            if (dtb.Rows.Count > 0)
            {
                Project model = new Project();
                model.ProjectId = (int)dr[0];
                model.Code = dr[1].ToString();
                model.Name = dr[2].ToString();
                model.Descr = dr[3].ToString();
                model.Manager = dr[4].ToString();
                model.Scope = dr[5].ToString();
                model.Date = (DateTime?)dr[6];
                model.Reason = dr[7].ToString();
                model.Object = dr[8].ToString();
                model.Target = dr[9].ToString();
                model.Plan = dr[10].ToString();
                model.Remarks = dr[11].ToString();
                model.Year = (int?)dr[12];
                model.StartDate = (DateTime?)dr[13];
                model.EndDate = (DateTime?)dr[14];
                model.GfmisCode = dr[15].ToString();
                model.TotalAmount = (decimal?)dr[16];
                model.JobSiteName = dr[17].ToString();
                model.SubAct = dr[18].ToString();
                model.Act = dr[19].ToString();
                model.OutPut = dr[20].ToString();

                models.Add(model);
            }

            return models;
        }

        public string[] GetProjectArea(int ProjectId)
        {
            DataTable dtb;
            string strSQL;
            string[] arrAreaName;

            strSQL = "SELECT concat(coalesce(opt3.bgm_option_name, '')"
                    + ", ' ', coalesce(opt2.bgm_option_name, '')"
                    + ", ' ', coalesce(opt.bgm_option_name, '')) as area_name"
                + " from bgm_project_option po"
                + " left join bgm_mas_option opt on po.bgm_option_seq = opt.bgm_option_seq"
                + " left join bgm_mas_option opt2 on opt.upper_bgm_option_seq = opt2.bgm_option_seq"
                + " left join bgm_mas_option opt3 on opt2.upper_bgm_option_seq = opt3.bgm_option_seq"
                + " where po.bgm_proj_seq = " + ProjectId
                + " and opt.bgm_option_type_seq = 1";
            dbManager.Open();
            dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            if (dtb.Rows.Count > 0)
            {
                arrAreaName = new string[dtb.Rows.Count];

                for (int i = 0; i < dtb.Rows.Count; i++)
                {
                    arrAreaName[i] = dtb.Rows[i][0].ToString();
                }

                return arrAreaName;
            }

            return null;
        }

        public string[] GetProjectBudget(int ProjectId)
        {
            DataTable dtb;
            string strSQL;
            string[] arrAreaName;

            strSQL = "SELECT concat(coalesce(opt3.bgm_option_name, '')"
                    + ", ' ', coalesce(opt2.bgm_option_name, '')"
                    + ", ' ', coalesce(opt.bgm_option_name, '')) as area_name"
                + " from bgm_project_option po"
                + " left join bgm_mas_option opt on po.bgm_option_seq = opt.bgm_option_seq"
                + " left join bgm_mas_option opt2 on opt.upper_bgm_option_seq = opt2.bgm_option_seq"
                + " left join bgm_mas_option opt3 on opt2.upper_bgm_option_seq = opt3.bgm_option_seq"
                + " where po.bgm_proj_seq = " + ProjectId
                + " and opt.bgm_option_type_seq = 2";
            dbManager.Open();
            dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            if (dtb.Rows.Count > 0)
            {
                arrAreaName = new string[dtb.Rows.Count];

                for (int i = 0; i < dtb.Rows.Count; i++)
                {
                    arrAreaName[i] = dtb.Rows[i][0].ToString();
                }

                return arrAreaName;
            }

            return null;
        }
    }

    [DataContract]
    public class Project
    {
        public Project()
        {
        }

        [DataMember]
        public int ProjectId { get; set; }

        [DataMember]
        public string Code { get; set; }

        [DataMember]
        public string Name { get; set; }

        [DataMember]
        public string Descr { get; set; }

        [DataMember]
        public string Manager { get; set; }

        [DataMember]
        public string Scope { get; set; }

        [DataMember]
        public DateTime? Date { get; set; }

        [DataMember]
        public string Reason { get; set; }

        [DataMember]
        public string Object { get; set; }

        [DataMember]
        public string Target { get; set; }

        [DataMember]
        public string Plan { get; set; }

        [DataMember]
        public string Remarks { get; set; }

        [DataMember]
        public int? Year { get; set; }

        [DataMember]
        public DateTime? StartDate { get; set; }

        [DataMember]
        public DateTime? EndDate { get; set; }

        [DataMember]
        public string GfmisCode { get; set; }

        [DataMember]
        public decimal? TotalAmount { get; set; }

        [DataMember]
        public string JobSiteName { get; set; }

        [DataMember]
        public string SubAct { get; set; }

        [DataMember]
        public string Act { get; set; }

        [DataMember]
        public string OutPut { get; set; }
    }
}
